CREATE       PROCEDURE [dbo].[asi_WorkItemQueueFetch]
@itemCount int = 3,
@category nvarchar(50) = null
AS

DECLARE @id uniqueidentifier
SET @id=newid()

DECLARE @tmp nvarchar(800)

SET @tmp = N'SELECT TOP ' + CAST(@itemCount as nvarchar(10)) + N' WorkItemQueueKey, SelectionLock INTO #pick '
SET @tmp = @tmp + N'FROM WorkItemQueue '
SET @tmp = @tmp + N'WHERE IsInteractive = 0 AND (SelectionLock IS NULL OR KeepAlive < dateadd(mi, - dbo.asi_GetWorkItemLockDuration(), getdate())) AND (BeginOn IS NULL OR BeginOn >= getdate()) '
IF DATALENGTH(@category) > 0
BEGIN
    SET @tmp = @tmp + N'AND Category LIKE ''' + @category + N'%'' '
END
SET @tmp = @tmp + N'ORDER BY Priority ASC, EnqueuedOn ASC '

SET @tmp = @tmp + N'UPDATE WorkItemQueue SET SelectionLock = ''' + CAST(@id AS nvarchar(40)) + N''', SelectedOn = getdate(), KeepAlive = getdate() '
SET @tmp = @tmp + N'FROM WorkItemQueue INNER JOIN #pick ON WorkItemQueue.WorkItemQueueKey = #pick.WorkItemQueueKey '
SET @tmp = @tmp + N'WHERE (WorkItemQueue.SelectionLock IS NULL OR WorkItemQueue.SelectionLock = #pick.SelectionLock) '

SET @tmp = @tmp + N'DROP TABLE #pick '

SET @tmp = @tmp + N'SELECT WorkItemQueueKey FROM WorkItemQueue WHERE SelectionLock = ''' + CAST(@id AS nvarchar(40)) + N''''

EXEC(@tmp)

GO